package main

import (
	"fmt"
	"log"

	"github.com/pgparser/pkg/parser"
)

func main() {
	fmt.Println("PostgreSQL Parser - SQL生成示例")
	fmt.Println("==============================")

	// 测试SQL解析和重新生成
	testCases := []struct {
		name string
		sql  string
	}{
		{
			name: "基本SELECT语句",
			sql:  "SELECT id, name FROM users WHERE age > 18;",
		},
		{
			name: "复杂SELECT语句",
			sql:  "SELECT u.name, COUNT(p.id) AS post_count FROM users u LEFT JOIN posts p ON u.id = p.user_id WHERE u.active = true GROUP BY u.id, u.name HAVING COUNT(p.id) > 5 ORDER BY post_count DESC LIMIT 10;",
		},
		{
			name: "INSERT语句",
			sql:  "INSERT INTO users (name, email, age) VALUES ('John Doe', 'john@example.com', 25);",
		},
		{
			name: "UPDATE语句",
			sql:  "UPDATE users SET name = 'Jane Smith', email = 'jane@example.com' WHERE id = 1;",
		},
		{
			name: "DELETE语句",
			sql:  "DELETE FROM users WHERE age < 18 OR active = false;",
		},
		{
			name: "子查询",
			sql:  "SELECT name FROM users WHERE id IN (SELECT user_id FROM orders WHERE total > 100);",
		},
		{
			name: "CASE表达式",
			sql:  "SELECT name, CASE WHEN age < 18 THEN 'minor' WHEN age >= 65 THEN 'senior' ELSE 'adult' END AS category FROM users;",
		},
		{
			name: "新操作符测试",
			sql:  "SELECT first_name || ' ' || last_name AS full_name FROM users WHERE email IS NOT NULL;",
		},
	}

	for i, tc := range testCases {
		fmt.Printf("\n测试用例 %d: %s\n", i+1, tc.name)
		fmt.Printf("原始SQL: %s\n", tc.sql)
		fmt.Println("---")

		// 解析SQL为AST
		ast, err := parser.ParseSQLToAST(tc.sql)
		if err != nil {
			log.Printf("解析错误: %v", err)
			continue
		}

		// 将AST重新生成为SQL
		generator := parser.NewSQLGenerator(false)
		generatedSQL := generator.Generate(ast)

		fmt.Printf("生成SQL: %s\n", generatedSQL)

		// 验证生成的SQL是否可以再次解析
		_, err = parser.ParseSQLToAST(generatedSQL)
		if err != nil {
			log.Printf("⚠️  生成的SQL无法重新解析: %v", err)
		} else {
			fmt.Println("✓ 生成的SQL可以成功重新解析")
		}

		// 比较原始SQL和生成SQL的结构是否一致
		if compareSQL(tc.sql, generatedSQL) {
			fmt.Println("✓ SQL结构保持一致")
		} else {
			fmt.Println("ℹ️  SQL格式已标准化")
		}
	}

	// 演示手动构建AST并生成SQL
	fmt.Println("\n=== 手动构建AST示例 ===")
	demonstrateManualASTConstruction()
}

// compareSQL 简单比较两个SQL语句的结构是否相似
func compareSQL(original, generated string) bool {
	// 解析两个SQL语句
	originalAST, err1 := parser.ParseSQLToAST(original)
	generatedAST, err2 := parser.ParseSQLToAST(generated)

	if err1 != nil || err2 != nil {
		return false
	}

	// 简单比较AST类型
	return fmt.Sprintf("%T", originalAST) == fmt.Sprintf("%T", generatedAST)
}

// demonstrateManualASTConstruction 演示手动构建AST并生成SQL
func demonstrateManualASTConstruction() {
	fmt.Println("手动构建一个SELECT语句的AST...")

	// 构建 SELECT id, name FROM users WHERE age > 18
	selectStmt := &parser.SelectStmt{
		Targets: []parser.Expression{
			&parser.ColumnRef{Column: "id"},
			&parser.ColumnRef{Column: "name"},
		},
		From: []parser.TableRef{
			{Name: "users"},
		},
		Where: &parser.BinaryExpr{
			Left:     &parser.ColumnRef{Column: "age"},
			Operator: parser.GT,
			Right:    &parser.Literal{Value: 18, Type: parser.IntegerLiteral},
		},
	}

	// 生成SQL
	generator := parser.NewSQLGenerator(false)
	generatedSQL := generator.Generate(selectStmt)
	fmt.Printf("手动构建的AST生成的SQL: %s\n", generatedSQL)

	// 演示复杂表达式构建
	fmt.Println("\n构建包含字符串连接和空值检查的查询...")
	complexStmt := &parser.SelectStmt{
		Targets: []parser.Expression{
			// first_name || ' ' || last_name AS full_name
			&parser.BinaryExpr{
				Left: &parser.BinaryExpr{
					Left:     &parser.ColumnRef{Column: "first_name"},
					Operator: parser.CONCAT,
					Right:    &parser.Literal{Value: " ", Type: parser.StringLiteral},
				},
				Operator: parser.CONCAT,
				Right:    &parser.ColumnRef{Column: "last_name"},
			},
		},
		From: []parser.TableRef{
			{Name: "users"},
		},
		Where: &parser.BinaryExpr{
			Left:     &parser.ColumnRef{Column: "email"},
			Operator: parser.NE, // 使用不等于操作符作为示例
			Right:    &parser.Literal{Value: nil, Type: parser.NullLiteral},
		},
	}

	complexSQL := generator.Generate(complexStmt)
	fmt.Printf("复杂表达式生成的SQL: %s\n", complexSQL)

	// 验证生成的SQL
	_, err := parser.ParseSQLToAST(complexSQL)
	if err != nil {
		log.Printf("⚠️  复杂SQL解析失败: %v", err)
	} else {
		fmt.Println("✓ 复杂SQL解析成功")
	}
}